When using a lookup column in a custom list in SharePoint, you have the option to “enforce relationship behavior” where you can prevent items from the lookup list from being deleted once they are selected, or you can cause a cascade-delete action to occur
One of the biggest problems we had in SharePoint 2007 was the fact that people could delete items from a list that is being used to drive a Lookup column somewhere else. Once they delete the item, the list that includes the Lookup column has a blank value
We can also use a Lookup column based on a separate list